package com.coderman.service;

import com.coderman.bean.DataBaseBean;
import com.coderman.config.DatabaseConfiguration;
import com.coderman.enums.ErrCodelEnum;
import com.coderman.mapper.DataBaseMapper;
import com.coderman.util.JsonResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * Created by ${fanchunshuai} on 2017-6-28.
 *
 * @version: V1.0
 * @Desc:
 * @Copyright (c) 2017 58到家-版权所有
 */
@Service
public class DataBaseService {
    @Autowired
    private DataBaseMapper dataBaseMapper;


    @Autowired
    private DatabaseConfiguration configuration;

    public String save(DataBaseBean dataBaseBean){
        dataBaseMapper.insert(dataBaseBean);
        return  JsonResult.parseRqtMsg(ErrCodelEnum.success.getCode(),"SUCCESS");
    }


    /**
     * 初始化数据库，绕过DAO层
     * @param id 数据库id
     * @return
     */
    public String createDataBase(Long id){
        DataBaseBean dataBaseBean = dataBaseMapper.getOne(id);
        //设置初始化状态
        dataBaseBean.setStatus(1);
        dataBaseMapper.update(dataBaseBean);
        String createDBSql = "CREATE DATABASE "+dataBaseBean.getDbName()+" default character set utf8 collate utf8_general_ci";
        String createUserSql = "insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) values ('"+dataBaseBean.getDbDomain()+"','"+dataBaseBean.getUsername()+"','"+dataBaseBean.getPassword()+"','','','')";
        String flushSql = "FLUSH PRIVILEGES";
        String grantUserSql = "grant all privileges on "+dataBaseBean.getDbName()+".* to "+dataBaseBean.getUsername()+"@"+
                dataBaseBean.getDbDomain()+" identified by '"+dataBaseBean.getPassword()+"' ";
        try {
            //1.创建MySQL账号
            exeSqlByJDBC(createUserSql);
            //2.刷新权限
            exeSqlByJDBC(flushSql);
            //3.创建数据库
            exeSqlByJDBC(createDBSql);
            //4.给该账号授权
            exeSqlByJDBC(grantUserSql);
        }catch (Exception e){
            e.printStackTrace();
            return JsonResult.parseRqtMsg(ErrCodelEnum.error.getCode(),"ERROR");
        }

        return JsonResult.parseRqtMsg(ErrCodelEnum.success.getCode(),"SUCCESS");
    }

    /**
     * 删除数据库
     * @param dataBaseName 数据库名称
     * @return
     */
    public String dropDataBase(String dataBaseName){
        String sql = "DROP DATABASE "+dataBaseName+" ";
        return exeSqlByJDBC(sql);
    }


    public List<DataBaseBean> getPageList(){
        return dataBaseMapper.getPageList();
    }


    /**
     * 绕过DAO层执行sql
     * @param sql
     * @return
     */
    private String  exeSqlByJDBC(String sql ){
        Connection conn = null;
        Statement stmt = null;
        try{
            //STEP 2: Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            //STEP 3: Open a connection
            System.out.println("Connecting to database...");
            String url = configuration.dataSource().getUrl();
            String userName = configuration.dataSource().getUsername();
            String password = configuration.dataSource().getPassword();

            conn = DriverManager.getConnection(url, userName, password);

            //STEP 4: Execute a query
            System.out.println("sql info = "+sql);
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        }catch(SQLException se){
            //Handle errors for JDBC
            se.printStackTrace();
            return  JsonResult.parseRqtMsg(ErrCodelEnum.error.getCode(),"ERROR");

        }catch(Exception e){
            //Handle errors for Class.forName
            e.printStackTrace();
            return  JsonResult.parseRqtMsg(ErrCodelEnum.error.getCode(),"ERROR");

        }finally{
            close(conn,stmt);
        }//end try
        System.out.println("Goodbye!");
        return  JsonResult.parseRqtMsg(ErrCodelEnum.success.getCode(),"SUCCESS");
    }

    /**
     * 根据db创建JDBC连接
     * @param id
     * @return
     */
    public Connection getDataBaseConnection(Long id){
        DataBaseBean dataBaseBean = dataBaseMapper.getOne(id);
        Connection conn = null;
        Statement stmt = null;
        try{
            //STEP 2: Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            //STEP 3: Open a connection
            System.out.println("Connecting to database...");

            String url = "jdbc:mysql://"+dataBaseBean.getDbDomain()+":"+dataBaseBean.getPort()+"/"+dataBaseBean.getDbName();
            conn = DriverManager.getConnection(url, dataBaseBean.getUsername(), dataBaseBean.getPassword());

            //STEP 4: Execute a query
            System.out.println("Creating connection...");
            System.out.println("connection created successfully...");
        }catch(SQLException se){
            //Handle errors for JDBC
            se.printStackTrace();
            System.out.println("connection created error1...");
            return null;
        }catch(Exception e){
            //Handle errors for Class.forName
            e.printStackTrace();
            System.out.println("connection created error2...");
            return null;
        }
        return conn;
    }


    /**
     * 关闭JDBC连接
     * @param conn
     * @param stmt
     */
    public void close(Connection conn,Statement stmt){
        //finally block used to close resources
        try{
            if(stmt!=null)
                stmt.close();
        }catch(SQLException se2){
        }// nothing we can do
        try{
            if(conn!=null)
                conn.close();
        }catch(SQLException se){
            se.printStackTrace();

        }//end finally try
    }

}
